package com.feib.ebilling.job.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;



import org.springframework.stereotype.Repository;


@Repository("transferFeedbackDao")
public class TransferFeedbackDao extends BaseDao {



	/**
	 * 檢查帳單資料是否已批次產生
	 * @param transferDate
	 * @return
	 */
	public List<String> hasProduced(List<String> channels,Date fbtime) {

		List<Object> args = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("select distinct CHANNEL from TRANSFER_FEEDBACK T")
		.append(" WHERE 1=1 ");	
		sql.append("and convert(varchar(8),T.fbtime,112) = ? ");

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(fbtime));	

		StringBuffer secs = new StringBuffer();
		for (int i=0; i < channels.size(); i++)
		{
			secs.append(" ?").append(",");
			args.add(channels.get(i));
		}
		sql.append(" AND T.CHANNEL IN (").append(secs.substring(0, secs.length()-1)).append(")");

		logger.debug("檢查是否已經有成功的資料: SQL=[{}], args=[{}]", sql,  args.toArray());


		List<Map<String, Object>> rds = this.getJdbcTemplate().queryForList(sql.toString(),args.toArray());
		List<String> result = new ArrayList<String>();
		for(Map<String, Object> rd : rds)
			result.add((String)rd.get("CHANNEL"));

		rds.clear();

		return result;



	}


	public int doTransferFeedback(List<String> channels,Date fbtime)
	{
		List<Object> args = new ArrayList<Object>();
		StringBuffer sql = new StringBuffer();
		sql.append("INSERT INTO TRANSFER_FEEDBACK(");
		sql.append("channel, txamt, fbtime,GROUP_NO,ACCOUNT_NO,CHANNEL_COUNT,COMMISSION_AMOUNT,TRANSFER_TYPE");
		sql.append(", CREATED_BY, CREATED_DATE ,GROUP_TYPE ");
		sql.append(")");
		sql.append("SELECT ");
		sql.append("  t. channel,sum( t.txamt) txamt ");
		sql.append("   ,t.fbtime ,t.groupno");
		sql.append("  ,t.account_no ,count(*) as CHANNEL_COUNT");
		sql.append("  , sum(fee) as  COMMISSION_AMOUNT ");
		sql.append("  ,case   channel   WHEN 'H'   THEN 2 ELSE 1 END  ");

		sql.append("  ,'TRANSFER_FEEDBACK',getdate() ,GROUP_TYPE");
		sql.append("  FROM (");
		sql.append("  select  case   channel ");//轉換成頻道
		sql.append(" WHEN 'POST'   THEN 'H'  ");
		sql.append(" WHEN '7111111'   THEN 'D' ");
		sql.append(" WHEN 'TFM'   THEN  'E' ");
		sql.append(" WHEN 'HILIFE'   THEN 'F' ");
		sql.append(" WHEN 'OKCVS'   THEN  'G' ");
		sql.append(" end  as   channel");
		sql.append("  ,DATEADD(day,DATEDIFF(day,0,fbtime),0) fbtime");
		sql.append("  ,txamt");
		sql.append("  ,case substring(userno,len(userno)-1,2) when '00' then substring(userno,1,14) else userno end as userno ");
		sql.append("  , POST_LEVEL1 as  post1,POST_LEVEL2  as post2,POST_LEVEL3  as post3 ");
		sql.append("  ,a.GROUP_NO as groupno,a.GROUP_TYPE");
		sql.append(", (case   channel ");
		sql.append("  WHEN 'POST' ");
		sql.append("  THEN ");
		sql.append("  case ");
		sql.append("  	WHEN f. txamt<=100  THEN  ISNULL(a.POST_LEVEL1,0)  ");
		sql.append("  	WHEN f. txamt<=1000 THEN  ISNULL( a.POST_LEVEL2 ,0) ");
		sql.append("  	WHEN  f.txamt >1000  THEN  ISNULL(a. POST_LEVEL3 ,0) ");
		sql.append("  ELSE 0 ");
		sql.append("  END ");
		sql.append("  ELSE ");
		sql.append("  ISNULL(GROUP_AMOUNT,0) ");
		sql.append("  END ");
		sql.append("  )  as fee ,account_no ");
		sql.append("from feedback_log f ");
		sql.append("inner join real_account r "); 
		/* 
		 * 郵局只有barcode3(=userno) 超商有 barcode2(=userno) barcode3 
		 * 使用userno=virtual_account_no來串帳單業者
		 */
		sql.append("on r.virtual_account_no = substring(f.userno, 1, len(r.virtual_account_no)) ");
		sql.append("left join app_group  a ");			
		sql.append(" on r.group_pk=a.group_pk ");
		sql.append("left join COMMISSION_CODE_TABLE c ");			
		sql.append(" on  COMMISSION_CODE=substring(barcdoe1,len(barcdoe1)-2,3)  ");		
		sql.append(" where feedback_type = 2 ");//回饋種類2 : thirdParty(便利商店、郵局等)
		sql.append("and  fbcode=1 ");//--1: 正常銷帳
		sql.append("and convert(varchar(8),f.fbtime,112) = ? ");

		SimpleDateFormat dateFormat = new SimpleDateFormat("yyyyMMdd");
		args.add(dateFormat.format(fbtime));	

		StringBuffer secs = new StringBuffer();
		for (int i=0; i < channels.size(); i++)
		{
			secs.append(" ?").append(",");
			args.add(channels.get(i));
		}
		sql.append(" AND CHANNEL IN (").append(secs.substring(0, secs.length()-1)).append(")");


		sql.append(") t ");

		sql.append("group by  t. channel  ,t.fbtime,t.account_no ,t.groupno,GROUP_TYPE ");

		logger.debug("doTransferFeedback: SQL=[{}], args=[{}]", sql,  args.toArray());

		
		return this.getJdbcTemplate().update(sql.toString(), args.toArray());

	}


}
